Testing Security Roles in Power BI Desktop

Course: Microsoft Power BI Training

Topic: Testing Security Roles in Power BI Desktop

Course Link: Microsoft Power BI Training at JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

Official Documentation: Microsoft Learn – Row-Level Security in Power BI

1. Introduction to Testing Security Roles

Testing security roles in Power BI Desktop is an important step in validating Row-Level Security (RLS) before publishing a report. RLS restricts the rows of data that users can access based on security roles and filter expressions defined in the semantic model.

When developing a Power BI report, a developer may need to ensure that a regional manager can view only their region's sales, an HR manager can access only authorized employee records, or a department head can view information related to their department. Testing security roles helps confirm that these restrictions work as expected.

Power BI Desktop provides the View as feature, which allows developers to simulate the effect of one or more security roles and, for dynamic RLS, test a specified user identity. This makes it possible to identify incorrect filters, missing relationships, and unexpected data visibility before deployment.

1.1 Why Is Security Role Testing Important?

• Verifies that RLS filters return the correct rows.
• Prevents unauthorized exposure of business data.
• Confirms that users can access the information they are supposed to see.
• Helps identify errors in DAX filter expressions.
• Validates relationships and filter propagation between tables.
• Reduces the risk of security problems after publishing.
• Helps developers test static and dynamic RLS configurations.

1.2 Real-World Example

Suppose a company maintains a single Power BI sales report for Mumbai, Delhi, Pune, and Bengaluru. Each regional manager should see only the sales records belonging to their region.

The developer creates security roles and tests each role in Power BI Desktop. If the Mumbai role displays Delhi sales records, the developer can correct the filter or model before the report is shared with business users.

2. Understanding Security Roles in Power BI

A security role defines the rules that determine which rows of data a user can access. These rules are commonly written as DAX filter expressions and applied to one or more tables in a Power BI semantic model.

2.1 What Is a Security Role?

A security role is a named collection of data-access rules. For example, a role called MumbaiManager can restrict the Sales table to records where the Region column equals Mumbai.

[Region] = "Mumbai"

When the role is applied, only rows that satisfy the filter should be available through the model.

2.2 Common Examples of Roles

2.3 Static and Dynamic Security Roles

Static RLS: Uses fixed filter values, such as [Region] = "Mumbai".

Dynamic RLS: Uses the signed-in user's identity and mapping data to determine the allowed rows. It commonly uses functions such as USERPRINCIPALNAME().

Both types should be tested before deployment. Static roles require verification of fixed filters, while dynamic roles also require validation of identity values and user-mapping relationships.

3. Understanding the View As Feature

The View as feature in Power BI Desktop allows a developer to view a report under the filtering rules of selected security roles. It is primarily used to simulate how RLS affects the model and report visuals.

3.1 What Can View As Test?

• Whether a security role filters the expected rows.
• Whether table and chart visuals reflect the filtered data.
• Whether totals and measures change appropriately.
• Whether relationships propagate security filters correctly.
• Whether a dynamic identity expression matches the expected user.
• Whether unexpected data remains visible under the selected role.

3.2 What View As Does Not Replace

• It does not replace testing the published semantic model in Power BI Service.
• It does not assign real users to roles in the service.
• It does not validate every authentication or workspace-permission scenario.
• It does not simulate all DirectQuery configurations, particularly those using single sign-on (SSO).
• It does not guarantee that the deployed model has the correct access permissions.

4. Preparing a Power BI Report for Security Testing

Before testing roles, make sure the report has a working data model, appropriate relationships, and security roles that reflect the intended access policy.

4.1 Required Components

• A Power BI Desktop PBIX file.
• A dataset containing the rows to protect.
• Security roles created in Power BI Desktop.
• Valid DAX filter expressions.
• Correct table relationships.
• Test data representing authorized and unauthorized records.
• A clear expected result for each role.

4.2 Example Sales Dataset

Consider the following Sales table:

This dataset contains records from four regions. The security tests should verify that each regional role displays only the rows belonging to its assigned region.

4.3 Define Expected Results Before Testing

Before using View as, document the expected output for each role. This makes it easier to identify incorrect results rather than simply checking whether the report looks different.

5. Creating Security Roles Before Testing

5.1 Open Manage Roles

1. Open the report in Power BI Desktop.
2. Select the Modeling tab on the ribbon.
3. Select Manage roles.
4. In the role-management window, select New.
5. Enter a meaningful role name.

5.2 Create a Mumbai Role

Select the Sales table and add this DAX filter expression:

[Region] = "Mumbai"

5.3 Create a Delhi Role

Create another role and apply this filter:

[Region] = "Delhi"

5.4 Create a Pune Role

Create a role named PuneManager and apply:

[Region] = "Pune"

5.5 Save the Roles

Check the role names and expressions. Confirm that each expression references the correct table column and value. Save the roles before proceeding to the View as test.

6. Step-by-Step: Testing Roles with View As

Step 1: Open the View As Window

In Power BI Desktop, select Modeling > View as. Power BI opens a dialog displaying the available security roles and relevant testing options.

Step 2: Select the Role to Test

Select the role you want to validate, such as MumbaiManager. If you want to test one role in isolation, select only that role.

Step 3: Apply the Role

Select OK to apply the role. Power BI Desktop renders the report using the security filters defined by the selected role.

Step 4: Inspect the Report

Review table visuals, charts, cards, slicers, and totals. Confirm that only the rows allowed by the role are represented in the results.

Step 5: Compare Against Expected Results

Compare the visible records and aggregate values with the test plan prepared earlier. For example, the Mumbai role should display sales IDs 101 and 103 and a total sales amount of 90,000.

Step 6: Exit the Role Simulation

After testing, return to the View as dialog or use the available exit option to stop viewing as the role. Confirm that the report returns to its normal development view.

7. Testing Multiple Security Roles

Power BI Desktop allows developers to select multiple roles during testing. This is useful when validating how combined role filters affect visible data.

7.1 Why Test Multiple Roles?

• A user may be assigned to multiple roles in Power BI Service.
• Users can receive role membership directly or through supported group membership.
• Multiple role assignments may expose more rows than a developer expects.
• Combined-role testing helps identify unintended access.

7.2 Example

Suppose the model has two roles:

// MumbaiManager
[Region] = "Mumbai"

// PuneManager
[Region] = "Pune"

If a user belongs to both roles, the combined filters can permit both Mumbai and Pune records. RLS role filters are generally additive, so a row allowed by either applicable role may be visible.

7.3 Testing Procedure

1. Open Modeling > View as.
2. Select both MumbaiManager and PuneManager.
3. Apply the selected roles.
4. Verify that the report shows only Mumbai and Pune data.
5. Confirm that Delhi and Bengaluru data remain excluded.
6. Record the result and compare it with the intended access policy.

Best practice: Prefer a role design that grants each user the intended complete access scope. Avoid assigning multiple roles without evaluating their combined effect.

8. Testing Dynamic RLS with Other User

Dynamic RLS uses the current user's identity to determine which rows should be visible. In Power BI Desktop, the View as window includes an Other user option for simulating an identity alongside the selected role.

8.1 Understanding USERPRINCIPALNAME()

The DAX function USERPRINCIPALNAME() returns the current user's User Principal Name (UPN). A UPN often resembles an email address, such as [email protected], but it should not automatically be assumed to match every user's email address.

8.2 Example User-Mapping Table

8.3 Example Dynamic Filter

Assume the UserRegion table maps user identifiers to their authorized regions. A role can filter the mapping table using:

UserRegion[UserEmail] = USERPRINCIPALNAME()

For the mapping to restrict the Sales table, the data model must have suitable relationships and filter propagation. A filter on a mapping table will not automatically filter an unrelated Sales table.

8.4 Test a Specific User

1. Open Modeling > View as.
2. Select the dynamic RLS role.
3. Select Other user.
4. Enter the test user's UPN, such as [email protected].
5. Apply the selected role and identity.
6. Inspect the report and verify the permitted rows.

In Desktop, the Other user option changes the results only when the security expressions use the supplied identity. For dynamic RLS, select the applicable role as well as specifying the test user.

8.5 Important Limitation

Testing an identity in Power BI Desktop is a simulation. It does not replace testing with an actual account after publishing, particularly for external guest users, embedded reports, or models with special authentication configurations.

9. Testing USERNAME() and USERPRINCIPALNAME()

Dynamic RLS can use either USERNAME() or USERPRINCIPALNAME(), depending on the design. The returned identity format can differ between Power BI Desktop and Power BI Service.

9.1 USERNAME()

In Power BI Desktop, USERNAME() can return an identity in a format such as DOMAIN\username. In the Power BI service, it generally returns a UPN-style identity.

9.2 USERPRINCIPALNAME()

USERPRINCIPALNAME() typically returns a UPN-style value such as [email protected]. This function is commonly used when a user-mapping table stores UPNs.

9.3 Identity Testing Checklist

• Confirm which DAX identity function the role uses.
• Check the exact value stored in the mapping table.
• Verify spelling, domain, and formatting.
• Test the identity supplied in the View as dialog.
• After publishing, validate the identity behavior in Power BI Service.
• Test guest users with their actual accounts when required.

10. Testing Whether Visuals Respect RLS

Testing should cover the entire report, not just one table visual. A role filter can affect charts, cards, matrices, tables, and other visuals that query the protected model.

10.1 Table Visuals

Check that each displayed record belongs to the authorized region or category. If a table displays record-level details, inspect its rows carefully.

10.2 Card Visuals

Check total sales, order counts, customer counts, and other key performance indicators. Confirm that the values reflect only the permitted records.

10.3 Charts

Check bar charts, line charts, pie charts, and other visualizations. Confirm that unauthorized categories are not displayed and that aggregate values are correct.

10.4 Slicers

Verify that slicers behave appropriately with the filtered data. Remember that slicers are not security controls: removing a slicer filter must not allow users to bypass RLS.

10.5 Measures and Totals

Measures that aggregate filtered tables should generally reflect the security context. If a total looks incorrect, inspect the underlying measure, relationships, and tables used by the calculation.

11. Testing Relationships and Filter Propagation

RLS filters can only restrict related tables as intended when the model's relationships and security-filter paths are configured correctly.

11.1 Active Relationships

Check that the relationship between the filtered dimension and the fact table is active where required. An inactive or missing relationship may prevent the intended security filter from reaching the data table.

11.2 Example Model

Region[RegionID] -> Sales[RegionID]

If the Region table is filtered to Mumbai, the relationship should be designed so that the filter restricts the related Sales rows.

11.3 Dynamic RLS Model

UserRegion[RegionID] -> Region[RegionID] -> Sales[RegionID]

This is a conceptual illustration of the desired filtering path. Actual relationship directions, cardinalities, and security-filter settings must be configured and tested for the model.

11.4 Relationship Testing Steps

1. Open Model view in Power BI Desktop.
2. Inspect relationships among mapping, dimension, and fact tables.
3. Verify the key columns and relationship cardinality.
4. Review filter directions and active relationship status.
5. Apply View as to the relevant role.
6. Check that all dependent visuals return the expected rows.

12. Testing Role Filters with DAX Expressions

RLS filter expressions are evaluated as Boolean conditions. Testing should confirm that each expression allows the correct records and excludes all other records.

12.1 Equality Condition

[Region] = "Mumbai"

Expected result: Only rows with Region equal to Mumbai are visible.

12.2 Multiple Regions

[Region] IN {"Mumbai", "Pune"}

Expected result: Rows from Mumbai and Pune are allowed.

12.3 Combined Conditions

[Region] = "Mumbai" && [SalesAmount] > 0

Expected result: Rows must belong to Mumbai and have a SalesAmount greater than zero.

12.4 Numeric Filter

[SalesAmount] >= 10000

Expected result: Rows with sales amounts of at least 10,000 are visible, subject to the role's other applicable filters.

12.5 No-Access Filter

FALSE()

Expected result: The filter returns no rows from the table to which it is applied. Use this pattern only when an intentionally empty result is part of the security design.

12.6 Testing Each Expression

• Verify the expression's syntax.
• Confirm the table and column references.
• Check the data types and exact values in the source data.
• Apply View as and compare the output with expected results.
• Test rows with blank, unexpected, or missing values.

13. Common RLS Testing Scenarios

Scenario 1: A Role Shows All Data

Problem: The MumbaiManager role displays records from every region.

Possible causes: The filter was applied to the wrong table, the expression is incorrect, the report is not using the expected model, or the test was performed without selecting the intended role.

Solution: Check the role expression, select the correct role, verify the filtered table, and confirm that the relationship path reaches the Sales table.

Scenario 2: A Role Shows No Data

Problem: The DelhiManager role displays empty visuals.

Possible causes: No rows match the filter, the region value is misspelled, the filter references the wrong column, or the model has an incorrect relationship.

Solution: Check the source values, inspect the DAX expression, and validate the relationship and filter path.

Scenario 3: Dynamic RLS Works for One User but Not Another

Problem: One test identity returns data while another returns no rows.

Possible causes: The second user has no mapping record, the UPN differs from the mapping value, or the user is not included in the intended role.

Solution: Verify the exact identity value, inspect the mapping table, and test the role with the correct UPN.

Scenario 4: Combined Roles Expose Additional Regions

Problem: A user sees records from multiple regions despite each individual role filtering one region.

Possible cause: The user is assigned to more than one role and the combined filters permit the union of the allowed rows.

Solution: Review direct and group-based role memberships, test combined roles, and redesign role assignments if the expanded access is not intended.

Scenario 5: Desktop Testing Passes but Service Testing Fails

Problem: The report appears correct in Desktop but does not behave as expected after publishing.

Possible causes: The role membership was not configured in the service, the identity format differs, or the user has elevated workspace permissions.

Solution: Verify the published role definition, role membership, identity values, and workspace permissions. Test with an actual Viewer-level account when appropriate.

14. Testing RLS Performance

RLS can affect query performance, especially when security filters involve complex DAX expressions, large mapping tables, or complicated relationships.

14.1 What to Measure

• Visual query duration before and after applying RLS.
• Refresh and report response times for large models.
• Impact of relationships and filter propagation.
• Performance differences among security roles.

14.2 Using Performance Analyzer

1. Open Power BI Desktop and select the Optimize or View area where Performance Analyzer is available in your version.
2. Open Performance Analyzer.
3. Start recording and refresh the report visuals.
4. Record the visual query durations without role simulation.
5. Apply the role using View as.
6. Repeat the recording and compare query durations.

This comparison helps identify whether RLS significantly affects report responsiveness. Performance results depend on the data model, storage mode, source system, and environment.

15. Testing RLS Before and After Publishing

Testing in Desktop is an important first step, but production deployment requires validation in Power BI Service as well.

15.1 Before Publishing

• Create the roles and DAX filters.
• Test each role with View as.
• Test dynamic identities where applicable.
• Verify all report pages, visuals, totals, and relationships.
• Document the expected results and any limitations.

15.2 After Publishing

• Confirm that the semantic model and report were published to the correct workspace.
• Assign users or supported security groups to the required roles.
• Use the service's Test as role feature where supported.
• Check workspace permissions and semantic model access.
• Validate with actual Viewer-level users when necessary.
• Confirm that the published report follows the intended security policy.

15.3 Important Workspace Permission Rule

In Power BI Service, RLS restricts data for users with Viewer-level access, but it does not apply to workspace Admin, Member, or Contributor roles in the same way. If users must be restricted by RLS, review their workspace and semantic model permissions carefully.

16. Limitations of View As

The View as feature is useful, but it has important limitations that developers should understand before relying on it as the only security test.

• DirectQuery with SSO: View as does not work for DirectQuery models with single sign-on enabled.
• Dynamic identity simulation: Desktop simulation is useful for testing identity-based rules, but it does not reproduce every real authentication scenario.
• Published role membership: Desktop testing does not assign real users to roles in Power BI Service.
• Workspace permissions: Role simulation does not replace validation of workspace and semantic model permissions.
• External users: Guest user identity formats may differ from the values expected by the mapping table.
• Some report features: The service's Test as role feature does not validate every report experience, including certain Q&A, Quick Insights, or Copilot experiences.

For models using DirectQuery with SSO, or scenarios involving external users, validate access using the appropriate actual user account and supported testing procedure.

17. RLS Testing Checklist

18. Practical Project: Test Regional Sales Security

Project Objective

Build a Power BI report and validate that each regional manager sees only the sales data permitted by their security role.

Required Tables

• Sales: SalesID, Region, Product, SalesAmount.
• UserRegion: UserEmail, Region.
• Region: RegionID, RegionName, if using a separate region dimension.

Project Steps

1. Load the required tables into Power BI Desktop.
2. Create the relationships needed to filter Sales data.
3. Build a report with total sales, sales by region, and a transaction table.
4. Create static roles for Mumbai, Delhi, and Pune.
5. Create a dynamic role if user-mapping data is available.
6. Use Modeling > View as to test each static role.
7. Use Other user with the dynamic role to test sample UPNs.
8. Compare visible rows and totals with expected results.
9. Test combined roles to understand their effect on access.
10. Publish the report and validate role assignments in Power BI Service.

Expected Outcome

Each role displays only the permitted data, all visuals reflect the filtered results, and any unexpected access is identified before production deployment.

19. Interview Questions and Answers

Q1. What is the purpose of View as in Power BI Desktop?

Answer: View as simulates security roles so developers can validate how RLS filters affect the rows and visuals in a report.

Q2. Where can you find View as?

Answer: It is available from the Modeling tab in Power BI Desktop.

Q3. How do you test a static RLS role?

Answer: Select the role in View as, apply it, and compare the displayed rows and measures against the expected results.

Q4. How do you test dynamic RLS?

Answer: Select the dynamic role and use Other user to supply the relevant UPN, then verify the filtered results.

Q5. Why might a dynamic role return no rows?

Answer: The user may be missing from the mapping table, the UPN may not match, or the relationships and filter expressions may be incorrect.

Q6. Can multiple roles be tested at once?

Answer: Yes. Multiple roles can be selected to inspect combined access, but the result may include rows allowed by any of the selected roles.

Q7. Does View as prove that Power BI Service permissions are correct?

Answer: No. Desktop testing validates model behavior, but service role assignments and workspace permissions must be tested separately.

Q8. Why should every report visual be tested?

Answer: Different visuals and measures may use different tables or calculations. Checking the entire report helps identify missing filter paths and incorrect totals.

Q9. Can View as test DirectQuery models with SSO enabled?

Answer: No. The View as feature does not work for DirectQuery models with single sign-on enabled.

Q10. What should be checked when a user sees too much data?

Answer: Check the selected roles, combined role memberships, DAX expressions, relationships, and workspace permissions.

20. Quick Revision Checklist

• RLS restricts rows based on security-role filters.
• Create roles using Modeling > Manage roles.
• Test roles using Modeling > View as.
• Select the intended role before evaluating the report.
• Use Other user to test dynamic RLS identities in supported scenarios.
• Compare visible rows and totals with documented expected results.
• Check all report visuals, measures, and relationships.
• Test combined roles because their filters can be additive.
• Investigate missing data, unexpected data, and identity mismatches.
• Publish and validate role assignments in Power BI Service.
• Review workspace permissions because RLS does not restrict elevated workspace roles in the same way as Viewer access.
• Use real-account testing for scenarios that Desktop simulation cannot fully reproduce.

21. Conclusion

Testing security roles in Power BI Desktop is a critical step in developing secure and dependable reports. The View as feature helps developers simulate static and dynamic RLS, inspect the resulting data, verify DAX filters, and identify problems with relationships and role combinations.

A thorough testing process should cover all roles, relevant user identities, report visuals, measures, and data relationships. After publishing, the security configuration must also be validated in Power BI Service, including role membership and workspace permissions.

To continue learning, explore the Microsoft Power BI Training course, register for a course demo, or visit the JustAcademy website.

Further Reading: Microsoft Learn – Row-Level Security Guidance